﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_Shared:DataAccess,IDALShared
    {
        #region Country
        public string InsertCountryDetails(EntityCountry Country)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Country]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Country.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@CountryName", DbType.String, Country.CountryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateCountryDetails(EntityCountry Country)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Country]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Country.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@CountryName", DbType.String, Country.CountryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteCountryDetails(Guid CountryId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Country]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, CountryId);                    
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityCountry> GetCountryDetails()
        {
            List<EntityCountry> CountryDetails = new List<EntityCountry>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Country]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return CountryDetails;
                            }
                            while (OReader.Read())
                            {
                                EntityCountry oCountry = new EntityCountry();
                                oCountry.CountryId = (Guid)OReader["CountryId"];
                                oCountry.CountryName = OReader["CountryName"].ToString();
                                CountryDetails.Add(oCountry);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return CountryDetails;
        
        }
        public EntityCountry GetCountry(Guid CountryId)
        {
            EntityCountry Country = new EntityCountry();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Country]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, CountryId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Country;
                            }
                            while (OReader.Read())
                            {                                
                                Country.CountryId = (Guid)OReader["CountryId"];
                                Country.CountryName = OReader["CountryName"].ToString();                                
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Country;

        }
        #endregion 
        #region State
        public string InsertStateDetails(EntityStates State)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_State]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, State.StateId);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, State.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@StateName", DbType.String, State.StateName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateStateDetails(EntityStates State)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_State]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, State.StateId);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, State.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@StateName", DbType.String, State.StateName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteStateDetails(Guid StateId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_State]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, StateId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityStates> GetStateDetails()
        {
            List<EntityStates> StateDetails = new List<EntityStates>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_State]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return StateDetails;
                            }
                            while (OReader.Read())
                            {
                                EntityStates oState = new EntityStates();
                                oState.StateId = (Guid)OReader["StateId"];
                                oState.CountryId = (Guid)OReader["CountryId"];
                                oState.Country = new EntityCountry();
                                oState.Country.CountryName = OReader["CountryName"].ToString();
                                oState.StateName = OReader["StateName"].ToString();
                                StateDetails.Add(oState);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return StateDetails;

        }
        public EntityStates GetState(Guid StateId)
        {
            EntityStates State = new EntityStates();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_State]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, StateId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return State;
                            }
                            while (OReader.Read())
                            {
                                State.StateId = (Guid)OReader["StateId"];
                                State.StateName = OReader["StateName"].ToString();
                                State.Country = new EntityCountry();
                                State.Country.CountryId = (Guid)OReader["CountryId"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return State;

        }
        #endregion
        #region Greade Settings
        public string InsertGrageSettings(EntityGrade Grade)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_GradeSettings]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@GradeName", DbType.String, Grade.GradeName);
                    SQLServer.AddInParameter(dbCommand, "@AmountToPay", DbType.String, Grade.AmountToPay);
                    SQLServer.AddInParameter(dbCommand, "@AmountToUse", DbType.String, Grade.AmountToUse);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@GradeRange", DbType.String, Grade.GradeRangeStart);
                    SQLServer.AddInParameter(dbCommand, "@GradeRangeEnd", DbType.String, Grade.GradeRangeEnd);
                    SQLServer.AddInParameter(dbCommand, "@AmountToSave", DbType.String, Grade.AmountToSave);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateGrageSettings(EntityGrade Grade)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_GradeSettings]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@GradeName", DbType.String, Grade.GradeName);
                    SQLServer.AddInParameter(dbCommand, "@GradeId", DbType.Guid, Grade.GradeId);
                    SQLServer.AddInParameter(dbCommand, "@AmountToPay", DbType.String, Grade.AmountToPay);
                    SQLServer.AddInParameter(dbCommand, "@AmountToUse", DbType.String, Grade.AmountToUse);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@GradeRange", DbType.String, Grade.GradeRangeStart);
                    SQLServer.AddInParameter(dbCommand, "@GradeRangeEnd", DbType.String, Grade.GradeRangeEnd);
                    SQLServer.AddInParameter(dbCommand, "@AmountToSave", DbType.String, Grade.AmountToSave);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteSettings(Guid GradeId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_GradeSettings]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@GradeId", DbType.Guid, GradeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityGrade> GetGrageSettings()
        {
            List<EntityGrade> GradeDetails = new List<EntityGrade>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_GradeSettings]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return GradeDetails;
                            }
                            while (OReader.Read())
                            {
                                EntityGrade Grade = new EntityGrade();
                                Grade.GradeId = (Guid)OReader["GradeId"];
                                Grade.GradeName = OReader["GradeName"].ToString();
                                Grade.GradeRangeEnd = OReader["GradeRangeEnd"].ToString();
                                Grade.GradeRangeStart = OReader["GradeRangeStart"].ToString();
                                Grade.Status = (int)OReader["Status"];
                                Grade.AmountToPay = OReader["AmountToPay"].ToString();
                                Grade.AmountToSave = OReader["AmountToSave"].ToString();
                                Grade.AmountToUse = OReader["AmountToUse"].ToString();
                                GradeDetails.Add(Grade);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return GradeDetails;
        }
        public EntityGrade GetGrageSetting(Guid GradeId)
        {
            EntityGrade Grade = new EntityGrade();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_GradeSettings]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@GreadeId", DbType.Guid, GradeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Grade;
                            }
                            while (OReader.Read())
                            {
                                Grade.GradeId = (Guid)OReader["GradeId"];
                                Grade.GradeName = OReader["GradeName"].ToString();
                                Grade.GradeRangeEnd = OReader["GradeRangeEnd"].ToString();
                                Grade.GradeRangeStart = OReader["GradeRangeStart"].ToString();
                                Grade.Status = (int)OReader["Status"];
                                Grade.AmountToPay = OReader["AmountToPay"].ToString();
                                Grade.AmountToSave = OReader["AmountToSave"].ToString();
                                Grade.AmountToUse = OReader["AmountToUse"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Grade;
        }
        #endregion
        #region Jobtitle
        public string InsertJobTitle(EntityJobtitle Jobtitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@JobTitleId", DbType.Guid, Jobtitle.JobTitleId);
                    SQLServer.AddInParameter(dbCommand, "@JobTitleName", DbType.String, Jobtitle.JobTitleName);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.Int32, Jobtitle.Organizationid);
                    SQLServer.AddInParameter(dbCommand, "@CanHaveMultiple", DbType.Boolean, Jobtitle.CanHaveMultiple);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateJobTitle(EntityJobtitle Jobtitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@JobTitleId", DbType.Guid, Jobtitle.JobTitleId);
                    SQLServer.AddInParameter(dbCommand, "@JobTitleName", DbType.String, Jobtitle.JobTitleName);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.Int32, Jobtitle.Organizationid);
                    SQLServer.AddInParameter(dbCommand, "@CanHaveMultiple", DbType.Boolean, Jobtitle.CanHaveMultiple);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteJobTitle(Guid JobtitleId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@JobTitleId", DbType.Guid, JobtitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityJobtitle> GetJobTitles(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityJobtitle> Jobtitles = new List<EntityJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Jobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityJobtitle Jobtitle = new EntityJobtitle();
                                Jobtitle.JobTitleId = (Guid)OReader["JobTitleId"];
                                Jobtitle.JobTitleName = OReader["JobTitleName"].ToString();
                                Jobtitle.Organizationid = (int)OReader["Organiztionid"];
                                Jobtitle.TotalRecords = (int)OReader["TotalRecords"];
                                Jobtitle.OrganizationName = OReader["OrganizationName"].ToString();
                                Jobtitle.CanHaveMultiple = (bool)OReader["CanHaveMultiple"];
                                Jobtitles.Add(Jobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Jobtitles;
        }
        public List<EntityJobtitle> GetJobTitles(int OrganizationId)
        {
            List<EntityJobtitle> Jobtitles = new List<EntityJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "TYPE");
                    SQLServer.AddInParameter(dbCommand, "@Organiztionid", DbType.Int32, OrganizationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Jobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityJobtitle Jobtitle = new EntityJobtitle();
                                Jobtitle.JobTitleId = (Guid)OReader["JobTitleId"];
                                Jobtitle.JobTitleName = OReader["JobTitleName"].ToString();
                                Jobtitle.Organizationid = (int)OReader["Organiztionid"];
                                Jobtitle.OrganizationName = OReader["OrganizationName"].ToString();
                                Jobtitle.CanHaveMultiple = (bool)OReader["CanHaveMultiple"];
                                Jobtitles.Add(Jobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Jobtitles;
        }
        public EntityJobtitle GetJobTitle(Guid JobtitleId)
        {
            EntityJobtitle Jobtitle = new EntityJobtitle();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleId", DbType.Guid, JobtitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Jobtitle;
                            }
                            while (OReader.Read())
                            {
                                Jobtitle.JobTitleId = (Guid)OReader["JobTitleId"];
                                Jobtitle.JobTitleName = OReader["JobTitleName"].ToString();
                                Jobtitle.Organizationid = (int)OReader["Organiztionid"];
                                Jobtitle.OrganizationName = OReader["OrganizationName"].ToString();
                                Jobtitle.CanHaveMultiple = (bool)OReader["CanHaveMultiple"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Jobtitle;
        }
        #endregion
        #region language
        public List<EntityLanguage> GetLanguages()
        {
            List<EntityLanguage> Langeuges = new List<EntityLanguage>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Langeuges;
                            }
                            while (OReader.Read())
                            {
                                EntityLanguage Language = new EntityLanguage();
                                Language.LanguageId = (Guid)OReader["LanguageId"];
                                Language.LanguageName = OReader["LanguageName"].ToString();
                                Language.LagnuageAbbrivation = OReader["LagnuageAbbrivation"].ToString();
                                Language.LanguageValue = (int)OReader["LanguageValue"];
                                Langeuges.Add(Language);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Langeuges;
        }
        public EntityLanguage GetLanguage(Guid LanguageId)
        {
            EntityLanguage Langeuge = new EntityLanguage();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, LanguageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Langeuge;
                            }
                            while (OReader.Read())
                            {
                                Langeuge.LanguageId = (Guid)OReader["LanguageId"];
                                Langeuge.LanguageName = OReader["LanguageName"].ToString();
                                Langeuge.LagnuageAbbrivation = OReader["LagnuageAbbrivation"].ToString();
                                Langeuge.LanguageValue = (int)OReader["LanguageValue"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Langeuge;
        }
        public List<EntityLanguage> GetLanguages(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityLanguage> Langeuges = new List<EntityLanguage>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Langeuges;
                            }
                            while (OReader.Read())
                            {
                                EntityLanguage Language = new EntityLanguage();
                                Language.LanguageId = (Guid)OReader["LanguageId"];
                                Language.LanguageName = OReader["LanguageName"].ToString();
                                Language.LagnuageAbbrivation = OReader["LagnuageAbbrivation"].ToString();
                                Language.LanguageValue = (int)OReader["LanguageValue"];
                                Language.TotalRecords = (int)OReader["TotalRecords"];
                                Langeuges.Add(Language);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Langeuges;
        }
        public string InsertLanguage(EntityLanguage Language)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@LanguageName", DbType.String, Language.LanguageName);
                    SQLServer.AddInParameter(dbCommand, "@LagnuageAbbrivation", DbType.String, Language.LagnuageAbbrivation);
                    SQLServer.AddInParameter(dbCommand, "@LanguageValue", DbType.String, Language.LanguageValue);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateLanguage(EntityLanguage Language)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, Language.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageName", DbType.String, Language.LanguageName);
                    SQLServer.AddInParameter(dbCommand, "@LagnuageAbbrivation", DbType.String, Language.LagnuageAbbrivation);
                    SQLServer.AddInParameter(dbCommand, "@LanguageValue", DbType.String, Language.LanguageValue);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteLanguage(Guid LanguageId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Language]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, LanguageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Comments
        public string insertComments(EntityComments Comments)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Comments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CommentMapId", DbType.Guid, Comments.CommentMapId);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, Comments.Name);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Comments.PhoneNo);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Comments.EmailId);
                    SQLServer.AddInParameter(dbCommand, "@Comments", DbType.String, Comments.Comment);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, Comments.Status);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Relationship
        public List<EntityRelationShip> GetUnMarriedRelationShip()
        {
            List<EntityRelationShip> RelationShips = new List<EntityRelationShip>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, "UNMARRIEDFAMILYONLY");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return RelationShips;
                            }
                            while (OReader.Read())
                            {
                                EntityRelationShip RelationShip = new EntityRelationShip();
                                RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                RelationShip.TotalCounts = (Int32)OReader["TotalCounts"];
                                RelationShips.Add(RelationShip);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return RelationShips;
        }
        public List<EntityRelationShip> GetRelationShip()
        {
            List<EntityRelationShip> RelationShips = new List<EntityRelationShip>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, "MARRIEDFAMILYONLY");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return RelationShips;
                            }
                            while (OReader.Read())
                            {
                                EntityRelationShip RelationShip = new EntityRelationShip();
                                RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                RelationShip.MarriedFamily = (Int32)OReader["MarriedFamily"];
                                RelationShip.TotalCounts = (Int32)OReader["TotalCounts"];
                                RelationShips.Add(RelationShip);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return RelationShips;
        }
        public EntityRelationShip GetRelationShipByID(Guid RelationshipId)
        {
            EntityRelationShip RelationShip = new EntityRelationShip();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@RelationShipId", DbType.Guid, RelationshipId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return RelationShip;
                            }
                            while (OReader.Read())
                            {
                                RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                RelationShip.MarriedFamily = (Int32)OReader["MarriedFamily"];
                                RelationShip.TotalCounts = (Int32)OReader["TotalCounts"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return RelationShip;
        }
        public List<EntityRelationShip> GetAllRelationShips(int PageNo, string SearchKey, string Sort, string SortMode)
        {
            List<EntityRelationShip> RelationShips = new List<EntityRelationShip>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return RelationShips;
                            }
                            while (OReader.Read())
                            {
                                EntityRelationShip RelationShip = new EntityRelationShip();
                                RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                RelationShip.MarriedFamily = (Int32)OReader["MarriedFamily"];
                                RelationShip.TotalCounts = (Int32)OReader["TotalCounts"];
                                RelationShips.Add(RelationShip);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return RelationShips;
        }
        public string InsertRelationShip(EntityRelationShip RelationShip)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@RelationshipName", DbType.String, RelationShip.RelationShipName);
                    SQLServer.AddInParameter(dbCommand, "@MarriedFamily", DbType.Int32, RelationShip.MarriedFamily);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateRelationShip(EntityRelationShip RelationShip)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@RelationshipName", DbType.String, RelationShip.RelationShipName);
                    SQLServer.AddInParameter(dbCommand, "@MarriedFamily", DbType.Int32, RelationShip.MarriedFamily);
                    SQLServer.AddInParameter(dbCommand, "@RelationshipId", DbType.Guid, RelationShip.RelationShipId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteRelationShip(Guid RelationShipId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Relationship]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@RelationshipId", DbType.Guid, RelationShipId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region DefaultConfig
        public string InsertDefaultConfig(EntityDefaultConfig DefaultConfig)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_DefaultConfig]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@KeyName", DbType.String, DefaultConfig.KeyName);
                    SQLServer.AddInParameter(dbCommand, "@KeyValue",DbType.String, DefaultConfig.KeyValue);
                    SQLServer.AddInParameter(dbCommand, "@KeyDesc", DbType.String, DefaultConfig.KeyDesc);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateDefaultConfig(EntityDefaultConfig DefaultConfig)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_DefaultConfig]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@KeyId", DbType.Guid, DefaultConfig.KeyId);
                    SQLServer.AddInParameter(dbCommand, "@KeyName", DbType.String, DefaultConfig.KeyName);
                    SQLServer.AddInParameter(dbCommand, "@KeyValue", DbType.String, DefaultConfig.KeyValue);
                    SQLServer.AddInParameter(dbCommand, "@KeyDesc", DbType.String, DefaultConfig.KeyDesc);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteDefaultConfig(Guid KeyId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_DefaultConfig]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@KeyId", DbType.Guid, KeyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityDefaultConfig> GetDefaultConfig()
        {
            List<EntityDefaultConfig> DefaultConfigs = new List<EntityDefaultConfig>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_DefaultConfig]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return DefaultConfigs;
                            }
                            while (OReader.Read())
                            {
                                EntityDefaultConfig DefaultConfig = new EntityDefaultConfig();
                                DefaultConfig.KeyId = (Guid)OReader["KeyId"];
                                DefaultConfig.KeyName = OReader["KeyName"].ToString();
                                DefaultConfig.KeyValue = OReader["KeyValue"].ToString();
                                DefaultConfig.KeyDesc = OReader["KeyDesc"].ToString();
                                DefaultConfigs.Add(DefaultConfig);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return DefaultConfigs;
        }
        public EntityDefaultConfig GetDefaultConfig(Guid DefaultConfigId)
        {
            EntityDefaultConfig DefaultConfig = new EntityDefaultConfig();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Jobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@Keyid", DbType.Guid, DefaultConfigId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return DefaultConfig;
                            }
                            while (OReader.Read())
                            {
                                DefaultConfig.KeyId = (Guid)OReader["KeyId"];
                                DefaultConfig.KeyName = OReader["KeyName"].ToString();
                                DefaultConfig.KeyValue = OReader["KeyValue"].ToString();
                                DefaultConfig.KeyDesc = OReader["KeyDesc"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return DefaultConfig;
        }
        #endregion
        #region Dateformat
        public List<EntityDateFormat> GetDateformat()
        {
            List<EntityDateFormat> DateFormats = new List<EntityDateFormat>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Dateformat]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return DateFormats;
                            }
                            while (OReader.Read())
                            {
                                EntityDateFormat DateFormat = new EntityDateFormat();
                                DateFormat.DateFormateId = (Guid)OReader["DateFormateId"];
                                DateFormat.DateFormatName = OReader["DateFormatName"].ToString();
                                DateFormat.DateFormatValue = OReader["DateFormatValue"].ToString();
                                DateFormat.CodeFormat = OReader["CodeFormat"].ToString();
                                DateFormat.JqueryFormat = OReader["JqueryFormat"].ToString();
                                DateFormats.Add(DateFormat);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return DateFormats;
        }
        #endregion
    }
}
